When connecting to MS SQL Server Database via Weblogic Datasource and using XA jdbc driver, the following error is thrown.

<Jun 3, 2014 5:16:49 AM PDT> <Error> <Console> <BEA-240003> <Console encountered the following error java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'master..xp_jdbc_open2'.
at weblogic.jdbc.sqlserverbase.ddb_.b(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddb_.a(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddb9.b(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddb9.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.v(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddq.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at weblogic.jdbc.sqlserver.ddj.m(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddel.e(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddel.a(Unknown Source)

 The cause behind the issue is that  the MS SQL Server was not installed with the Stored procedures to enable JTA/XA

Solution

To connect to SQL Server via XA Driver from WLS Datasource you need to install Stored Procedures for JTA

To use JDBC distributed transactions through JTA, your system administrator should use the following procedure to install Microsoft SQL Server JDBC XA procedures.

This procedure must be repeated for each MS SQL Server installation that will be involved in a distributed transaction.

To install stored procedures for JTA:

1. Copy the appropriate sqljdbc.dll and instjdbc.sql files from the WL_HOME\server\lib directory to the SQL_Server_Root/bin directory of the MS SQL Server database server, where WL_HOME is the directory in which WebLogic server is installed, typically c:\Oracle\Middleware\wlserver_10.x.

  Note:
  If you are installing stored procedures on a database server with multiple Microsoft SQL Server instances, each running SQL Server instance must be able to locate the sqljdbc.dll file.

Therefore the sqljdbc.dll file needs to be anywhere on the global PATH or on the application-specific path. For the application-specific path, place the sqljdbc.dll file into the :\Program Files\Microsoft SQL Server\MSSQL$\Binn directory for each instance.

 2. From the database server, use the ISQL utility to run the instjdbc.sql script. As a precaution, have your system administrator back up the master database before running instjdbc.sql. At a command prompt, use the following syntax to run instjdbc.sql:

  ISQL -Usa -Psa_password -Sserver_name -ilocation\instjdbc.sql

  where:

  sa_password is the password of the system administrator.

  server_name is the name of the server on which SQL Server resides.

  location is the full path to instjdbc.sql. (You copied this script to the SQL_Server_Root/bin directory in step 1.)

  The instjdbc.sql script generates many messages. In general, these messages can be ignored; however, the system administrator should scan the output for any messages that may indicate an execution error. The last message should indicate that instjdbc.sql ran successfully. The script fails when there is insufficient space available in the master database to store the JDBC XA procedures or to log changes to existing procedures.